package model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import model.bean.DailyLog;
import model.bean.Service;
import model.bean.ServiceInfo;
import model.bean.TourInfo;
import model.bean.TourKind;
import model.bean.TourTemp;
import model.bean.TourTemplate;

public class TourUpdateDAO extends BaseDAO {
	ResultSet rs = null;
    Connection connection = null;
    PreparedStatement statement = null;
    boolean result = false;
	public boolean insertTour(String tourTempName, int tourKind,
			double tourPrice, int usual) {
		// TODO Auto-generated method stub
		connection = super.getConnection();
		String query = "insert into TOURMAU values (?,?,?,?)";
		if (connection != null) {
			try {
				statement = connection.prepareStatement(query);
				statement.setString(1, tourTempName);
				statement.setInt(2, tourKind);
				statement.setDouble(3, tourPrice);
				statement.setInt(4,usual);
				int n = statement.executeUpdate();
				result = n==1?true:false;
				System.out.println("them tour mau thanh cong: "+result);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return result;
	}
	public ArrayList<TourKind> getTourKind() {
		// TODO Auto-generated method stub
		connection = getConnection();
		String query = "select * from LoaiTour";
		ArrayList<TourKind> kind = new ArrayList<TourKind>();
		if (connection != null) {
			try {
				statement = connection.prepareStatement(query);
				rs = statement.executeQuery();
				TourKind temp = null;
				
				while (rs.next()) {
					temp = new TourKind();
					temp.setKindIndex(rs.getInt(1));
					temp.setKindName(rs.getString(2));
					kind.add(temp);
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return kind;
	}
	public ArrayList<TourTemp> getListTour() {
		// TODO Auto-generated method stub
		connection = getConnection();
		String query = "select * from TourMau";
		ArrayList<TourTemp> listTour = new ArrayList<TourTemp>();
		
		//process
		if (connection != null) {
			try {
				statement = connection.prepareStatement(query);
				rs = statement.executeQuery();
				TourTemp temp = null;
				while (rs.next()) {
					temp = new TourTemp();
					temp.setTourTmpIndex(rs.getInt("MaTourMau"));
					temp.setTourTmpName(rs.getString("TenTourMau"));
					temp.setTourTmpKind(rs.getInt("MaLoaiTour"));
					temp.setTourTmpPrice(rs.getDouble("GiaThamKhao"));
					temp.setUsual(rs.getInt("TanSuat"));
					System.out.println(temp.getTourTmpName());
					listTour.add(temp);
				}
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return listTour;
	}
	public boolean updateTour(int tourTempIndex, String tourTempName,
			int tourKind, double tourPrice, int usual) {
		
		// TODO Auto-generated method stub
				connection = super.getConnection();
				System.out.println(tourTempName+" : "+tourPrice);
				String query = "Update TourMau Set TenTourMau = ?," +
						" MaLoaiTour = ?, " +
						"GiaThamKhao = ?," +
						"TanSuat=? " +
						"where MaTourMau = ?";

				if (connection != null) {
					try {
						statement = connection.prepareStatement(query);
						statement.setString(1, tourTempName);
						statement.setInt(2, tourKind);
						statement.setDouble(3, tourPrice);
						statement.setInt(4,usual);
						statement.setInt(5, tourTempIndex);
						int n = statement.executeUpdate();
						result = n==1?true:false;
						System.out.println("them tour mau thanh cong: "+result);
						
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
				}
				return result;
	}
	public ArrayList<DailyLog> getDailyLog(int tourId) {
		// TODO Auto-generated method stub
		connection = super.getConnection();
		ArrayList<DailyLog> dailyLog = new ArrayList<DailyLog>();
		
		//query
		String query = "select * from NHATTRINH where MaTourMau = ?";
		if (connection != null) {
			try {
				statement = connection.prepareStatement(query);
				statement.setInt(1, tourId);
				rs = statement.executeQuery();
				DailyLog tmp = null;
				while (rs.next()) {
					tmp = new DailyLog();
					tmp.setDailyId(rs.getInt("MaNhatTrinh"));
					tmp.setDayCome(1);
					tmp.setTourId(tourId);
					tmp.setHotelId(rs.getInt("MaKhachSan"));
					tmp.setTrafficId(rs.getInt("MaNhaXe"));
					tmp.setResId(rs.getInt("MaNhaHang"));
					tmp.setTarget(rs.getInt("MaDiaDiem"));
					dailyLog.add(tmp);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return dailyLog;
	}
	public ArrayList<Service> getService(int i) {
		// TODO Auto-generated method stub
		connection = super.getConnection();
		ArrayList<Service> service = new ArrayList<Service>();
		String query = "";
		String kind = "";
		
		switch (i) {
			case 1:{
				kind = "KHACHSAN";
				query = "select * from KHACHSAN";
				break;
			}
			case 2: kind = "NHAHANG";
					query = "select * from NHAHANG";
					break;
			case 3: {
				kind = "NHAXE";
				query = "select * from NHAXE";
				break;
			}
			default: {
				kind = "DIADIEMTHAMQUAN";
				query = "select * from DIADIEMTHAMQUAN";
				break;
			}
		}
		
		if (connection != null) {
			try {
				statement = connection.prepareStatement(query);
				//statement.setString(1, kind);
				rs = statement.executeQuery();
				Service tmp ;
				while (rs.next()) {
					tmp = new Service();
					tmp.setKind(kind);
					tmp.setIndex(rs.getInt(1));
					tmp.setName(rs.getString(2));
					service.add(tmp);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return service;
	}
	public boolean insertDailyLog(int tourId, int target, int traffic, int res,
			int hotel, int day) {
		// TODO Auto-generated method stub
		connection = super.getConnection();
		String query = "insert into NHATTRINH values (?,?,?,?,?,?)";
		if (connection != null) {
			try {
				statement = connection.prepareStatement(query);
				statement.setInt(1, hotel);
				statement.setInt(2, res);
				statement.setInt(3, traffic);
				statement.setString(4, "2014-08-03");
				statement.setInt(5, tourId);
				statement.setInt(6, target);
				int n = statement.executeUpdate();
				result = n==1?true:false;
				System.out.println("them tour mau thanh cong: "+result);
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return result;
	}
	 public ArrayList<TourInfo> getTourList(String dayFirst, String dayUp) {
	        // TODO Auto-generated method stub
	        ResultSet rs = null;
	        TourInfo tour = null;
	        connection = super.getConnection();
	        String sql = "Select * FROM Chitiettour where ngaybatdau >= ? and " +
	                     "ngayketthuc <= ?";
	        ArrayList<TourInfo> listTour = new ArrayList<TourInfo>();
	        if (connection != null) {
	            try {
	                statement = connection.prepareStatement(sql);
	                statement.setString(1, dayFirst);
	                statement.setString(2, dayUp);
	                
	                rs = statement.executeQuery();
	                int count = 0;
	                //get result
	                while (rs.next()) {
	                    tour = new TourInfo();
	                    tour.setTourId(rs.getInt("MaChiTietTour"));
	                    tour.setTempTourId(rs.getInt("MaTourMau"));
	                    tour.setTourName(rs.getString("TenTour"));
	                    tour.setDayStart(rs.getString("NgayBatDau"));
	                    tour.setDayEnd(rs.getString("NgayKetThuc"));
	                    count ++;
	                    listTour.add(tour);
	                }
	               
	            } catch (SQLException e) {
	                
	                e.printStackTrace();
	            } finally {
	                 //closeConnection();
	                 System.out.println("da dong ket noi getTourList");
	             }
	        }
	        else {
	            
	            //do nothing
	        }
	        return listTour;
	    }
	 public ArrayList<TourTemplate> getTempList() {
	        TourTemplate tourTmp = null;
	        ResultSet rs = null;
	        TourInfo tour = null;
	        
	        connection = super.getConnection();
	        String sql = "select matourmau,tentourmau from tourmau";
	        ArrayList<TourTemplate> listTempTour = new ArrayList<TourTemplate>();
	        
	        if (connection!=null) {
	            try {
	                statement = connection.prepareStatement(sql);
	                rs = statement.executeQuery();
	                while (rs.next()) {
	                    tourTmp = new TourTemplate();
	                    tourTmp.setTempTourIndex(rs.getInt("matourmau"));
	                    tourTmp.setTempTourName(rs.getString("tentourmau"));
	                    listTempTour.add(tourTmp);
	                }
	            } catch (SQLException e) {
	                // TODO Auto-generated catch block
	                e.printStackTrace();
	            } finally {
	                closeConnection();
	                System.out.println("da dong ket noi getTempList");

	            }
	            
	        } else {
	            //do nothing
	        }
	        
	        return listTempTour;
	    }
	 public TourInfo getTourInfo(int tourIndex) {
	        ResultSet rs = null;
	        TourInfo tour = null;
	        
	        connection = new BaseDAO().getConnection();
	        String sql = "Select * FROM Chitiettour where Machitiettour = ?";
	        if (connection != null) {
	            try {
	                statement = connection.prepareStatement(sql);
	                statement.setInt(1, tourIndex);
	                rs = statement.executeQuery();
	                int count = 0;
	                //get result
	                while (rs.next()) {
	                    tour = new TourInfo();
	                    tour.setTourId(rs.getInt("MaChiTietTour"));
	                    tour.setTempTourId(rs.getInt("MaTourMau"));
	                    tour.setTourName(rs.getString("TenTour"));
	                    tour.setDayStart(rs.getString("NgayBatDau"));
	                    tour.setDayEnd(rs.getString("NgayKetThuc"));
	                }
	               
	            } catch (SQLException e) {
	                
	                e.printStackTrace();
	            } finally {
	                 closeConnection();
	                 System.out.println("da dong ket noi getTourInfo");
	             }
	        }
	        else {
	            
	            //do nothing
	        }
	        return tour;
	    }
	 public boolean updateTourExac(int tourIndex, int tourTempIndex, String dayStart, String dayEnd) {
	        // TODO Auto-generated method stub
	        boolean update = false;
	        connection = super.getConnection();
	        System.out.println("ket noi thanh cong");
	        
	        String sql = "Update Chitiettour " +
	                        "set MaTourMau = ?," +
	                        " NgayBatDau = ?," +
	                        " NgayKetThuc = ?" +
	                        " where MaChiTietTour = ?";
	        
	        
	        System.out.println(tourTempIndex+" "+tourIndex+" "+dayStart+" "+ dayEnd);
	        System.out.println("da thuc hien DAO toi day");
	        
	        if (connection != null) {
	            try {
	                  //set values
	                statement = connection.prepareStatement(sql);  
	                  statement.setInt(1, tourTempIndex);
	                  statement.setString(2, dayStart);
	                  statement.setString(3, dayEnd);
	                  statement.setInt(4, tourIndex);
	                               
	                int rs2 = statement.executeUpdate();
	                System.out.println(rs2);
	                update = rs2>0?true:false;
	            } catch (SQLException e) {
	                // TODO Auto-generated catch block
	                e.printStackTrace();
	            }finally {
	                

	            }
	        }
	        return update;
	    }
	 public ArrayList<ServiceInfo> getServiceList(int type, String dayStart,
	            String dayEnd, String order) {
	        // TODO Auto-generated method stub
	        connection = super.getConnection();
	        ArrayList<ServiceInfo> listService = new ArrayList<ServiceInfo>();
	        String sql = "";
	        ResultSet rs = null;
	        
	        switch(type){
	        case 1: sql = "select * from getTraffic(?,?)";break;
	        case 2: sql = "select * from getRestaurant(?,?)";break;
	        case 3: sql = "select * from getHotel(?,?)";break;
	        }
	        System.out.println(sql);
	        if (connection != null) {
	        try {
	            statement = connection.prepareStatement(sql);
	            statement.setString(1, dayStart);
	            statement.setString(2, dayEnd);
	            rs = statement.executeQuery();
	            ServiceInfo temp = null;
	            while(rs.next()) {
	                temp = new ServiceInfo();
	                temp.setSvName(rs.getString("htName"));
	                temp.setSvIndex(rs.getInt("htIndex"));
	                temp.setSvAddress(rs.getString("htAddress"));
	                temp.setSvEmail(rs.getString("htEmail"));
	                temp.setSvCount(rs.getInt("countUser"));
	                listService.add(temp);
	                //System.out.println(temp.getSvName());
	            }
	        } catch (SQLException e) {
	            // TODO Auto-generated catch block
	            e.printStackTrace();
	        }
	        }
	        return listService;
	    }


}
